using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using Deloitte.MSExcel;
using Deloitte.Windows.Forms;
namespace Deloitte.Windows.Controls
{    
    /// <summary>
    /// Summary description for ctlExcelExport.
    /// </summary>
    public class ExcelExportControl : System.Windows.Forms.UserControl
    {
        private Deloitte.MSExcel.IExportToExcel xlExport;        
        private Deloitte.MSExcel.DataTableExcelExport xlDataTableExport;
        private System.Windows.Forms.OpenFileDialog openFileDialog1;
        private Deloitte.Windows.Forms.ExcelNumberFormatMappingForm frmExcelNumberFormatMapping;
        private Button exportButton;
        private ErrorProvider errorProvider1;
        private DataGridView dataGridView1;
        private ProgressBar progressBar1;
        private DataGridViewButtonColumn colSelectWorkbook;
        private DataGridViewTextBoxColumn colExcelWorkbookPath;
        private DataGridViewComboBoxColumn colExcelWorksheet;
        private DataGridViewTextBoxColumn colStartingExportRange;
        private DataGridViewTextBoxColumn colColumnOffset;
        private DataGridViewTextBoxColumn colRowOffset;
        private DataGridViewButtonColumn colNumberFormats;
        private DataGridViewCheckBoxColumn colIncludeColumnHeaders;
        private DataGridViewCheckBoxColumn colAutoFilter;
        private Label errorLabel;
        private BackgroundWorker backgroundWorker1;
        private Timer timer1;
        private IContainer components;
    
        public ExcelExportControl(IExportToExcel xlExportable)
        {
            // This call is required by the Windows.Forms Form Designer.
            InitializeComponent();
            this.xlExport = xlExportable;
            this.CommonConstructorCode();
        }
        public ExcelExportControl()
        {
            // This call is required by the Windows.Forms Form Designer.
            InitializeComponent();
            this.CommonConstructorCode();
        }
        public ExcelExportControl(DataTable dt)
        {
            // This call is required by the Windows.Forms Form Designer.
            InitializeComponent();
            this.XLExport = new MSExcel.DataTableExcelExport(dt);
            CommonConstructorCode();
        }
        public IExportToExcel XLExport
        {
            get { return this.xlExport; }
            set { this.xlExport = value; }
        }
        
        private void CommonConstructorCode()
        {
            try
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(this.dataGridView1);
                this.dataGridView1.Rows.Add(row);
                
                this.dataGridView1["colAutoFilter", 0].Value = true;
                if (this.XLExport is MSExcel.DataTableExcelExport) {
                    AddDataTableExportColumns();
                    this.progressBar1.Enabled = true;
                    this.backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged);
                    this.backgroundWorker1.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker1_RunWorkerCompleted);                    
                }      
               
            }
            catch (Exception ex) { ExceptionHandling.ShowMessageBox(ex); }
        }

        void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {            
            this.progressBar1.Value = e.ProgressPercentage;
        }


        void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.timer1.Enabled = false;            
        }

        private void AddDataTableExportColumns()
        {
            this.dataGridView1["colIncludeColumnHeaders", 0].Value = true;
            this.dataGridView1["colRowOffset", 0].Value = 1;
            this.dataGridView1["colColumnOffset", 0].Value = 1;
            //set the reference to the xlDataTableExport for the Progress Bar
            this.progressBar1.Visible = true;
            this.xlDataTableExport = (MSExcel.DataTableExcelExport)this.xlExport;
        }
        public ExcelExportControl(DataView dv)
        {
            // This call is required by the Windows.Forms Form Designer.
            InitializeComponent();
            this.XLExport = new MSExcel.DataTableExcelExport(dv.Table);
            CommonConstructorCode();
            //			ConvertDataViewToDataTable(dv);
        }
        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code
        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle5 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle6 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle7 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle8 = new System.Windows.Forms.DataGridViewCellStyle();
            this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
            this.exportButton = new System.Windows.Forms.Button();
            this.errorProvider1 = new System.Windows.Forms.ErrorProvider(this.components);
            this.dataGridView1 = new System.Windows.Forms.DataGridView();
            this.colSelectWorkbook = new System.Windows.Forms.DataGridViewButtonColumn();
            this.colExcelWorkbookPath = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.colExcelWorksheet = new System.Windows.Forms.DataGridViewComboBoxColumn();
            this.colStartingExportRange = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.colColumnOffset = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.colRowOffset = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.colNumberFormats = new System.Windows.Forms.DataGridViewButtonColumn();
            this.colIncludeColumnHeaders = new System.Windows.Forms.DataGridViewCheckBoxColumn();
            this.colAutoFilter = new System.Windows.Forms.DataGridViewCheckBoxColumn();
            this.progressBar1 = new System.Windows.Forms.ProgressBar();
            this.errorLabel = new System.Windows.Forms.Label();
            this.backgroundWorker1 = new System.ComponentModel.BackgroundWorker();
            this.timer1 = new System.Windows.Forms.Timer(this.components);
            ((System.ComponentModel.ISupportInitialize)(this.errorProvider1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
            this.SuspendLayout();
            // 
            // exportButton
            // 
            this.exportButton.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
            this.exportButton.Location = new System.Drawing.Point(7, 76);
            this.exportButton.Name = "exportButton";
            this.exportButton.Size = new System.Drawing.Size(75, 23);
            this.exportButton.TabIndex = 29;
            this.exportButton.Text = "E&xport";
            this.exportButton.UseVisualStyleBackColor = true;
            this.exportButton.Click += new System.EventHandler(this.exportButton_Click);
            // 
            // errorProvider1
            // 
            this.errorProvider1.ContainerControl = this;
            // 
            // dataGridView1
            // 
            this.dataGridView1.AllowUserToAddRows = false;
            this.dataGridView1.AllowUserToDeleteRows = false;
            this.dataGridView1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
                        | System.Windows.Forms.AnchorStyles.Right)));
            this.dataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells;
            this.dataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.DisplayedCells;
            this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
            this.colSelectWorkbook,
            this.colExcelWorkbookPath,
            this.colExcelWorksheet,
            this.colStartingExportRange,
            this.colColumnOffset,
            this.colRowOffset,
            this.colNumberFormats,
            this.colIncludeColumnHeaders,
            this.colAutoFilter});
            this.dataGridView1.Location = new System.Drawing.Point(8, 6);
            this.dataGridView1.Name = "dataGridView1";
            this.dataGridView1.RowTemplate.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            this.dataGridView1.Size = new System.Drawing.Size(692, 66);
            this.dataGridView1.TabIndex = 0;
            this.dataGridView1.CellContentClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView1_CellContentClick);
            // 
            // colSelectWorkbook
            // 
            this.colSelectWorkbook.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridViewCellStyle5.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
            dataGridViewCellStyle5.BackColor = System.Drawing.Color.Silver;
            this.colSelectWorkbook.DefaultCellStyle = dataGridViewCellStyle5;
            this.colSelectWorkbook.HeaderText = "Select Excel Workbook";
            this.colSelectWorkbook.Name = "colSelectWorkbook";
            this.colSelectWorkbook.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            this.colSelectWorkbook.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
            this.colSelectWorkbook.Text = "Select...";
            this.colSelectWorkbook.ToolTipText = "Select an Excel Workbook with the Open File Dialog";
            this.colSelectWorkbook.UseColumnTextForButtonValue = true;
            this.colSelectWorkbook.Width = 113;
            // 
            // colExcelWorkbookPath
            // 
            this.colExcelWorkbookPath.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            this.colExcelWorkbookPath.HeaderText = "Excel Workbook Path";
            this.colExcelWorkbookPath.Name = "colExcelWorkbookPath";
            this.colExcelWorkbookPath.ReadOnly = true;
            this.colExcelWorkbookPath.Width = 107;
            // 
            // colExcelWorksheet
            // 
            this.colExcelWorksheet.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            this.colExcelWorksheet.HeaderText = "Excel Worksheet";
            this.colExcelWorksheet.Name = "colExcelWorksheet";
            this.colExcelWorksheet.ToolTipText = "The Excel Sheet to export the data to.  This column is populated when an Excel Wo" +
                "rkbook is selected";
            this.colExcelWorksheet.Width = 70;
            // 
            // colStartingExportRange
            // 
            this.colStartingExportRange.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            this.colStartingExportRange.HeaderText = "Starting Export Range";
            this.colStartingExportRange.MaxInputLength = 10;
            this.colStartingExportRange.Name = "colStartingExportRange";
            this.colStartingExportRange.ToolTipText = "An Excel Range Address (A1, C3, etc.)";
            this.colStartingExportRange.Width = 87;
            // 
            // colColumnOffset
            // 
            this.colColumnOffset.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridViewCellStyle6.Format = "N0";
            dataGridViewCellStyle6.NullValue = "0";
            this.colColumnOffset.DefaultCellStyle = dataGridViewCellStyle6;
            this.colColumnOffset.HeaderText = "Column Offset";
            this.colColumnOffset.Name = "colColumnOffset";
            this.colColumnOffset.ToolTipText = "Number of columns between each exported column";
            this.colColumnOffset.Width = 84;
            // 
            // colRowOffset
            // 
            this.colRowOffset.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridViewCellStyle7.Format = "N0";
            dataGridViewCellStyle7.NullValue = "0";
            this.colRowOffset.DefaultCellStyle = dataGridViewCellStyle7;
            this.colRowOffset.HeaderText = "Row Offset";
            this.colRowOffset.Name = "colRowOffset";
            this.colRowOffset.ToolTipText = "Number of rows between each exported row";
            this.colRowOffset.Width = 72;
            // 
            // colNumberFormats
            // 
            this.colNumberFormats.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridViewCellStyle8.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
            dataGridViewCellStyle8.BackColor = System.Drawing.Color.Silver;
            this.colNumberFormats.DefaultCellStyle = dataGridViewCellStyle8;
            this.colNumberFormats.HeaderText = "Select Number Formats";
            this.colNumberFormats.Name = "colNumberFormats";
            this.colNumberFormats.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            this.colNumberFormats.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
            this.colNumberFormats.Text = "Select...";
            this.colNumberFormats.ToolTipText = "Displays the form to map columns to number formats";
            this.colNumberFormats.UseColumnTextForButtonValue = true;
            this.colNumberFormats.Width = 117;
            // 
            // colIncludeColumnHeaders
            // 
            this.colIncludeColumnHeaders.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            this.colIncludeColumnHeaders.HeaderText = "Export Column Headers";
            this.colIncludeColumnHeaders.Name = "colIncludeColumnHeaders";
            this.colIncludeColumnHeaders.Width = 98;
            // 
            // colAutoFilter
            // 
            this.colAutoFilter.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.DisplayedCells;
            this.colAutoFilter.HeaderText = "Add Auto Filter";
            this.colAutoFilter.Name = "colAutoFilter";
            this.colAutoFilter.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            this.colAutoFilter.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
            this.colAutoFilter.Width = 87;
            // 
            // progressBar1
            // 
            this.progressBar1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
            this.progressBar1.BackColor = System.Drawing.SystemColors.ControlLightLight;
            this.progressBar1.ForeColor = System.Drawing.SystemColors.ActiveCaption;
            this.progressBar1.Location = new System.Drawing.Point(436, 76);
            this.progressBar1.Name = "progressBar1";
            this.progressBar1.Size = new System.Drawing.Size(264, 23);
            this.progressBar1.Style = System.Windows.Forms.ProgressBarStyle.Continuous;
            this.progressBar1.TabIndex = 30;
            this.progressBar1.Visible = false;
            // 
            // errorLabel
            // 
            this.errorLabel.AutoSize = true;
            this.errorLabel.Location = new System.Drawing.Point(88, 88);
            this.errorLabel.Name = "errorLabel";
            this.errorLabel.Size = new System.Drawing.Size(0, 11);
            this.errorLabel.TabIndex = 31;
            // 
            // backgroundWorker1
            // 
            this.backgroundWorker1.WorkerReportsProgress = true;
            this.backgroundWorker1.DoWork += new System.ComponentModel.DoWorkEventHandler(this.backgroundWorker1_DoWork);
            // 
            // timer1
            // 
            this.timer1.Enabled = true;
            this.timer1.Tick += new System.EventHandler(this.timer1_Tick);
            // 
            // ExcelExportControl
            // 
            this.AutoScroll = true;
            this.Controls.Add(this.errorLabel);
            this.Controls.Add(this.progressBar1);
            this.Controls.Add(this.dataGridView1);
            this.Controls.Add(this.exportButton);
            this.Font = new System.Drawing.Font("Tahoma", 6.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.Name = "ExcelExportControl";
            this.Size = new System.Drawing.Size(707, 105);
            ((System.ComponentModel.ISupportInitialize)(this.errorProvider1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
            this.ResumeLayout(false);
            this.PerformLayout();

        }
        #endregion


        private void exportButton_Click(object sender, EventArgs e)
        {
            try
            {                
                SetExcelExportProperties();
                if (this.xlExport is MSExcel.DataTableExcelExport)
                {
                    //todo: remove this line
                    this.xlDataTableExport.IsPatientFormDisplayed = true;
                    this.progressBar1.Value = 0;
                    this.progressBar1.Minimum = 0;                          
                    this.progressBar1.Maximum = this.xlDataTableExport.TotalRows;
                    this.xlDataTableExport.ExcelRowExportedEvent += new ExcelRowExportedEventHandler(xlDataTableExport_ExcelRowExportedEvent);                     
                }
                this.backgroundWorker1.RunWorkerAsync();
            }
            catch (Exception) { this.errorProvider1.SetError(this.errorLabel, "Error exporting to Excel.  Please try selecting a new workbook."); }
        }

        void xlDataTableExport_ExcelRowExportedEvent(ExcelRowExportedEventArgs e)
        {
            this.progressBar1.Value = this.xlDataTableExport.ExportedRows;
        }        
            
        private void SetExcelExportProperties()
        {            
            SetExcelWorksheet();
            SetExcelRange();
            SetAutoFilter();
            if(this.XLExport is MSExcel.DataTableExcelExport)
            {
                SetIsColumnHeadersExported();
            }
        }

        private void SetAutoFilter()
        {
            try
            {
                DataGridViewCheckBoxCell cellCheck = (DataGridViewCheckBoxCell)this.dataGridView1["colAutoFilter", 0];
                this.XLExport.IsAutoFilter = (bool)cellCheck.Value;
            }
            catch { this.XLExport.IsAutoFilter = false; }
        }
        private void SetIsColumnHeadersExported()
        {
            try
            {
                DataGridViewCheckBoxCell cellCheck = (DataGridViewCheckBoxCell)this.dataGridView1["colIncludeColumnHeaders", 0];
                this.XLExport.IsAutoFilter = (bool)cellCheck.Value;
            }
            catch { this.XLExport.IsAutoFilter = false; }
        }
        private void SetExcelWorksheet()
        {
            try
            {
                DataGridViewComboBoxCell cellCombo = (DataGridViewComboBoxCell)this.dataGridView1["colExcelWorksheet", 0];
                this.XLExport.XLWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)this.XLExport.XLWorkbook.Worksheets[cellCombo.Value.ToString()];
            }
            catch(Exception ex){throw new Exception(ex.ToString());}
        }
        private void SetExcelRange()
        {
            try
            {
                DataGridViewTextBoxCell cellText = (DataGridViewTextBoxCell)this.dataGridView1["colStartingExportRange", 0];
                this.XLExport.StartingRange = (Microsoft.Office.Interop.Excel.Range)this.XLExport.XLWorksheet.get_Range((string)cellText.Value, System.Type.Missing);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }
        private bool SetExcelWorkbook()
        {
            try
            {
                if (this.openFileDialog1 == null) this.openFileDialog1 = new OpenFileDialog();
                this.openFileDialog1.Title = @"Open Excel Workbook for Export";
                this.openFileDialog1.Filter = @"Excel Workbooks (*.xls)|*.xls";
                //todo: add a filter on the dialog for excel files
                if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    this.XLExport.XLWorkbook = this.XLExport.XLApplication.Workbooks.Open(this.openFileDialog1.FileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                }
                return true;
            }
            catch
            { //Hannon.Common.ExceptionHandling.ShowMessageBox(ex); 
                this.errorProvider1.SetError(this.dataGridView1, "Error setting the Excel Workbook: " + this.openFileDialog1.FileName);
                return false;
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                if (SetExcelWorkbook() == true)
                {
                    this.dataGridView1["colExcelWorkbookPath", e.RowIndex].Value = this.XLExport.XLWorkbook.Name;
                    string[] sheets = ExcelHelper.GetSheetNames(this.XLExport.XLWorkbook);
                    DataGridViewComboBoxCell comboCell = (DataGridViewComboBoxCell)this.dataGridView1["colExcelWorksheet", e.RowIndex];
                    comboCell.Items.Clear();
                    foreach (string sheet in sheets) { comboCell.Items.Add(sheet); }                    
                }
            }
            else if (e.ColumnIndex == 6)
            {
                string[] dataSourceName = new string[this.XLExport.NumberFormatMapping.Count];
                int i = 0;
                IEnumerator keys = this.XLExport.NumberFormatMapping.Keys.GetEnumerator();
                while(keys.MoveNext())
                {
                    dataSourceName[i]= keys.Current.ToString();
                    i++;
                }               
                if (this.frmExcelNumberFormatMapping == null) { frmExcelNumberFormatMapping = new ExcelNumberFormatMappingForm(dataSourceName); }
                if (frmExcelNumberFormatMapping.ShowDialog() == DialogResult.OK)
                {
                    this.XLExport.NumberFormatMapping = frmExcelNumberFormatMapping.XLNumberFormatMapping;
                }
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {            
            this.timer1.Enabled = true;
            this.xlExport.ExportToExcel();            
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.progressBar1.Value = this.xlDataTableExport.ExportedRows;
        }         
    }
}